﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using GovernmentOfficeProject.LevelDataAccessModel;
using System.Data.SqlClient;
using GovernmentOfficeProject.SysTools;
using System.Text;
namespace GovernmentOfficeProject.LevelDataAccess
{
    public class DAL_SME_SCHEDULE
    {
        /// <summary>
        /// 根据用户ID,开始日期，结束日期 获得日程信息
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="startDate">开始日期</param>
        /// <param name="endDate">结束日期</param>
        /// <returns></returns>
        public List<Entity_SME_Schedule> getScheduleListByUserIdAndDate(decimal userId,string startDate,string endDate,bool isLdCalendar)
        {
            List<Entity_SME_Schedule> list = new List<Entity_SME_Schedule>();
            string sql = "select * from [SME_SCHEDULE] t where t.sceUSID = @userId and t.sceStartTime>= @startDate and t.sceEndTime <= @endDate";
            if(isLdCalendar)
            {
                sql = sql + " and sceStatus = 3";
            }
            SqlParameter[] values = new SqlParameter[]{
                    new SqlParameter("@userId",userId),
                    new SqlParameter("@startDate",startDate),
                    new SqlParameter("@endDate",endDate)
                };
            DataSet ds = DAL_DBGM.GetDataSet(sql,values);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                list.Add(getScheduleByDataRow(row,"yyyy\\/MM\\/dd HH:mm:ss"));
            }
            return list;
        }

       /// <summary>
        /// 获得日程分页数据
       /// </summary>
       /// <param name="calendar">查询对象</param>
       /// <param name="currentPage">当前页</param>
       /// <param name="pageSize">每页显示条数</param>
       /// <returns></returns>
        public List<Entity_SME_Schedule> getSchedulePaginationList(Entity_SME_Schedule calendar, int currentPage, int pageSize)
        {
            List<Entity_SME_Schedule> list = new List<Entity_SME_Schedule>();
            string sql = @"SELECT TOP {0} * FROM SME_SCHEDULE WHERE (sceID NOT IN (SELECT TOP {1} sceID FROM SME_SCHEDULE WHERE 1=1 {2} ORDER BY sceID)) {3} ORDER BY sceStartTime,sceID"
                    .FormatWith(pageSize, (currentPage - 1) * pageSize, getConditionSql(calendar), getConditionSql(calendar));
            DataSet ds = DAL_DBGM.GetDataSet(sql);
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                list.Add(getScheduleByDataRow(row, "yyyy-MM-dd HH:mm"));
            }
            return list;
        }

        /// <summary>
        /// 获得数据总数
        /// </summary>
        /// <param name="calendar"></param>
        /// <returns></returns>
        public int getAllCount(Entity_SME_Schedule calendar)
        {
            string sql = "select count(*) COUNT from SME_SCHEDULE where 1=1 {0}".FormatWith(this.getConditionSql(calendar));
            return Convert.ToInt16(DAL_DBGM.GetDataSet(sql).Tables[0].Rows[0]["COUNT"]);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Entity_SME_Schedule getScheduleById(decimal id)
        {
            if (id == 0) throw new Exception("getScheduleById-->> Id is null!");
            string sql = "select * from SME_SCHEDULE t where t.sceID = "+id;
            DataRow row = DAL_DBGM.GetDataSet(sql).Tables[0].Rows[0];
            Entity_SME_Schedule schedule = null;
            if (row != null)
            {
                schedule = new Entity_SME_Schedule();
                schedule.SceID = (decimal)row["sceID"];
                schedule.UserId = (decimal)row["sceUSID"];
                schedule.Title = (string)row["sceTitle"];
                schedule.Content = (string)row["sceContent"];
                schedule.StartTime = DateTimeUtil.Date2String((DateTime)row["sceStartTime"], "yyyy/MM/dd HH:mm");
                schedule.EndTime = DateTimeUtil.Date2String((DateTime)row["sceEndTime"], "yyyy/MM/dd HH:mm");
                schedule.Status = (decimal)row["sceStatus"];
                schedule.Color = (string)row["sceColor"];
                schedule.IsAllDayEvent = (decimal)row["sceIsAllDayEvent"];
                schedule.Location = (string)row["sceLocation"];
                schedule.CreateTime = row["sceCreateTime"] == DBNull.Value  ? null : DateTimeUtil.Date2String((DateTime)row["sceCreateTime"], "yyyy-MM-dd HH:mm") ;
                schedule.ModifyTime = row["sceModifyTime"] == DBNull.Value ? null : DateTimeUtil.Date2String((DateTime)row["sceModifyTime"], "yyyy-MM-dd HH:mm") ;
                schedule.CreateUserId = (decimal)row["sceCreateUSID"];
                schedule.ModifyUserId = (decimal)row["sceModifyUSID"];
                if (row["sceBackup1"] != DBNull.Value)
                    schedule.NoEndTime = Convert.ToInt16(row["sceBackup1"]);
            }
            return schedule;
        }



       /// <summary>
        /// 根据ID更新日程
       /// </summary>
       /// <param name="id"></param>
       /// <param name="calendar"></param>
       /// <returns></returns>
        public bool updateScheduleById(decimal id, Entity_SME_Schedule calendar)
        {
            if(calendar == null)throw new Exception("The Update Object is null!");
            StringBuilder  sql = new StringBuilder();
            if (calendar.Title != null && calendar.Title.Length > 0)
            {
                sql.Append(" sceTitle = '").Append(calendar.Title).Append("'").Append(",");
            }

            if (calendar.Content != null && calendar.Content.Length > 0)
            {
                sql.Append(" sceContent = '").Append(calendar.Content).Append("'").Append(",");
            }

            if (calendar.StartTime != null && calendar.StartTime.Length > 0)
            {
                sql.Append(" sceStartTime = '").Append(calendar.StartTime).Append("'").Append(",");
            }

            if (calendar.EndTime != null && calendar.EndTime.Length > 0)
            {
                sql.Append(" sceEndTime = '").Append(calendar.EndTime).Append("'").Append(",");
            }

            if (calendar.Location != null && calendar.Location.Length > 0)
            {
                sql.Append(" sceLocation = '").Append(calendar.Location).Append("'").Append(",");
            }

            if (calendar.Color != null && calendar.Color.Length > 0)
            {
                sql.Append(" sceColor = '").Append(calendar.Color).Append("'").Append(",");
            }

            if (calendar.Status > 0)
            {
                sql.Append(" sceStatus = ").Append(calendar.Status).Append(",");
            }
            
            sql.Append(" sceIsAllDayEvent =").Append(calendar.IsAllDayEvent).Append(",");
            //未知结束时间
            sql.Append(" sceBackup1 = '").Append(calendar.NoEndTime).Append("',");
            sql.Append(" sceModifyUSID =").Append(calendar.ModifyUserId).Append(",");
            sql.Append(" sceModifyTime ='").Append(DateTimeUtil.Date2String(DateTime.Now, "yyyy-MM-dd HH:mm")).Append("',");
            string result = "update SME_SCHEDULE set "+ sql.ToString().Substring(0, sql.Length - 1) + " where sceID = "+id;

            return DAL_DBGM.UpdateInfo(result);
        }

        /// <summary>
        /// 根据ID 快速更新
        /// 只更新时间和修改时间及修改人
        /// </summary>
        /// <param name="id"></param>
        /// <param name="calendar"></param>
        /// <returns></returns>
        public bool quicktUpdate(Entity_SME_Schedule calendar)
        {
            string sql = @"update SME_SCHEDULE set sceStartTime = '{0}',sceEndTime = '{1}' ,sceModifyUSID = {2} ,sceModifyTime = '{3}' where sceID = {4}";
            sql = sql.FormatWith(calendar.StartTime, 
                                calendar.EndTime, 
                                calendar.ModifyUserId, 
                                DateTimeUtil.Date2String(DateTime.Now, "yyyy-MM-dd HH:mm"),
                                calendar.SceID);
            return DAL_DBGM.UpdateInfo(sql);
        }


        /// <summary>
        /// 保存日程
        /// </summary>
        /// <param name="calendar"></param>
        /// <returns></returns>
        public bool SaveSchedule(Entity_SME_Schedule calendar)
        {
            string sql = @"insert into SME_SCHEDULE(sceUSID,sceTitle,sceContent,sceStartTime,sceEndTime,sceLocation,sceIsAllDayEvent,sceColor,sceStatus,sceCreateUSID,sceCreateTime,sceBackup1) values({0},'{1}','{2}','{3}','{4}','{5}',{6},'{7}',{8},{9},'{10}','{11}')";

            sql = sql.FormatWith(calendar.UserId,   
                                    calendar.Title,        
                                    calendar.Content,      
                                    calendar.StartTime,    
                                    calendar.EndTime,      
                                    calendar.Location,     
                                    calendar.IsAllDayEvent,
                                    calendar.Color,                 
                                    calendar.Status,
                                    calendar.CreateUserId,
                                    DateTimeUtil.Date2String(DateTime.Now, "yyyy-MM-dd HH:mm"),
                                    calendar.NoEndTime
                                    );
            return DAL_DBGM.InsertInfo(sql);
        }


        /// <summary>
        /// 根据ID删除日程
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool deleteScheduleById(long id)
        {
            string sql = "delete SME_SCHEDULE where sceID = @id";
            SqlParameter[] values = new SqlParameter[]{
                 new SqlParameter("@id",id)
            };
            return DAL_DBGM.DeleteInfo(sql, values);
        }

        /// <summary>
        /// 获得自增ID
        /// 用于添加成功后获取ID
        /// </summary>
        /// <returns></returns>
        public  decimal getIdentCurrent()
        {
            string sql = "SELECT IDENT_CURRENT('SME_SCHEDULE') id";
            DataSet ds = DAL_DBGM.GetDataSet(sql);
            decimal id = Convert.ToDecimal(ds.Tables[0].Rows[0]["id"]);
            return id;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="row"></param>
        /// <param name="dateFormatStr"></param>
        /// <returns></returns>
        private Entity_SME_Schedule getScheduleByDataRow(DataRow row,string dateFormatStr)
        {
            Entity_SME_Schedule schedule = new Entity_SME_Schedule();
            schedule.SceID = (decimal)row["sceID"];
            schedule.UserId = (decimal)row["sceUSID"];
            schedule.Title = (string)row["sceTitle"];
            schedule.Content = (string)row["sceContent"];
            schedule.StartTime = DateTimeUtil.Date2String((DateTime)row["sceStartTime"], dateFormatStr);
            schedule.EndTime = DateTimeUtil.Date2String((DateTime)row["sceEndTime"], dateFormatStr);
            schedule.Status = (decimal)row["sceStatus"];
            schedule.Color = (string)row["sceColor"];
            schedule.IsAllDayEvent = (decimal)row["sceIsAllDayEvent"];
            schedule.Location = (string)row["sceLocation"];
            return schedule;
        }

        /// <summary>
        /// 获得查询条件
        /// </summary>
        /// <param name="calendar"></param>
        /// <returns></returns>
        private string getConditionSql(Entity_SME_Schedule calendar)
        {
            StringBuilder querySql = new StringBuilder();
            if (calendar.UserId > 0)
            {
                querySql.Append(" and sceUSID = ").Append(calendar.UserId);
            }
            if (!String.IsNullOrEmpty(calendar.StartTime))
            {
                querySql.Append(" and sceStartTime >= '").Append(calendar.StartTime).Append("'");
            }
            if(!String.IsNullOrEmpty(calendar.EndTime))
            {
                querySql.Append(" and sceEndTime <= '").Append(calendar.EndTime).Append("'");
            }
            if (!String.IsNullOrEmpty(calendar.Title))
            {
                querySql.Append(" and sceTitle like '%").Append(calendar.Title).Append("%'");
            }
            if(calendar.Status > 0)
            {
                querySql.Append(" and sceStatus = ").Append(calendar.Status);
            }
            return querySql.ToString();
        }
    }

}
